﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Activation;
using System.Text;
using NHibernate;
using NHibernate.Transform;
using Thaire.Investment.BL.DTO;
using Thaire.Investment.BL.Filter;
using Thaire.Investment.BL.report_dto;
using Thaire.Investment.BL.ScreenData;
using Thaire.Investment.Domains.Transaction;
using Thaire.Investment.Repository;

namespace Thaire.Investment.BL
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class ReportService : IReportService
    {


        #region IReportService Members

        public IList<report_dto.KeyValueDTO> GetMaster(string type)
        {
            IList<KeyValueDTO> results = null;

            if (type == "company")
            {
                string sql = @"
                            SELECT [COMPANY_ID]  Keyid
                              ,[COMPANY_NAME_TH] Val    
                          FROM [COMPANY]
                        ";
                results = queryKeyVal(sql);
            }
            else if (type == "issuer")
            {
                string sql = @"
                           SELECT [ISSUER_ID] Keyid
                            ,[ISSUER_NAME] Val
     
                        FROM [ISSUER]
                        ";
                results = queryKeyVal(sql);
            }


            return results;
        }
        public IList<report_dto.KeyValueDTO> GetMasterMode(string type, string mode)
        {
            IList<KeyValueDTO> results = null;

            if (type == "company")
            {
                string sql = @"
                            SELECT [COMPANY_ID]  Keyid
                              ,[COMPANY_NAME_TH] Val    
                          FROM [COMPANY] where  [COMPANY_ID] =
                        "+mode;
                results = queryKeyVal(sql);
            }
            else if (type == "issuer")
            {
                string sql = @"
                           SELECT [ISSUER_ID] Keyid
                            ,[ISSUER_NAME] Val
     
                        FROM [ISSUER]
                        ";
                results = queryKeyVal(sql);
            }


            return results;
        }
        private IList<KeyValueDTO> queryKeyVal(string sql)
        {


            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(sql);
            IList<KeyValueDTO> results = sqlQuery
      .SetResultTransformer(Transformers.AliasToBean(typeof(KeyValueDTO)))
      .List<KeyValueDTO>();
            return results;
        }

        #endregion

        #region IReportService Members


        public IList<ReportEquityDTO> GetReportEquity(ReportCriteriaDTO criteria)
        {

            string where1 = "";
            if (int.Parse(criteria.company) != 0)
            {
                where1 += " and EQUITY_HEADER.EQUITY_COMPANY_ID =" + criteria.company;

            }
            //if (int.Parse(criteria.financialInstrument) != 0)
            //{
            //    query += " and DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID=" + criteria.financialInstrument;
            //}
            if (int.Parse(criteria.assetClass == null ? "0" : criteria.assetClass) != 0)
            {
                // where1 += " and DEPOSIT_HEADER.ASSET_CLASS_ID=" + criteria.assetClass;
            }
            if (int.Parse(criteria.issuer == null ? "0" : criteria.issuer) != 0)
            {
                where1 += " and upper(EQUITY_HEADER.SYMBOL) like'%" + criteria.issuer.ToUpper() + "%'";

            }
            if (int.Parse(criteria.keepAt == null ? "0" : criteria.keepAt) != 0)
            {
                //none
            }
            if (criteria.atatus != "0")
            {
                where1 += " and EQUITY_TRANSACTION.[TRANSACTION_TYPE]	 ='" + criteria.atatus + "'";
            }
            if (int.Parse(criteria.related == null ? "0" : criteria.related) != 0)
            {
                where1 += " and  EQUITY_HEADER.[RELATED_ID] 	 ='" + criteria.atatus + "'";
            }

            if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;

                float m_t = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.timeToMaturityYearTo) + m_t;

                where1 += " and ROUND( CAST(DATEDIFF(month, EQUITY_TRANSACTION.[TRADE_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                where1 += " and ROUND( CAST(DATEDIFF(month, EQUITY_TRANSACTION.[TRADE_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where1 += " >=" + ym;

            }
            else if (int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearTo) + m;
                where1 += " and ROUND( CAST(DATEDIFF(month, EQUITY_TRANSACTION.[TRADE_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where1 += " <=" + ym;
            }

            if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;
                ym_f *= -1;

                float m_t = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.selectAfterMaturedYearTo) + m_t;
                ym_t *= -1;
                where1 += " and ROUND( CAST(DATEDIFF(month, EQUITY_TRANSACTION.[TRADE_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                ym *= -1;
                where1 += " and  ROUND( CAST(DATEDIFF(month, EQUITY_TRANSACTION.[TRADE_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += " >=" + ym;
            }

            if (int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.selectAfterMaturedYearTo) + m;
                ym *= -1;
                where1 += " and  ROUND( CAST(DATEDIFF(month, EQUITY_TRANSACTION.[TRADE_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += " <=" + ym;

            }

            if (String.IsNullOrEmpty(criteria.symbol) == false)
            {
                where1 += " and upper(EQUITY_HEADER.SYMBOL) like'%" + criteria.symbol.ToUpper() + "%'";
            }

            if (String.IsNullOrEmpty(criteria.tranDateFrom) == false && String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where1 += " and EQUITY_TRANSACTION.[TRADE_DATE] between convert(datetime, '" + criteria.tranDateFrom + "', 103) and ";
                where1 += "  convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateFrom) == false)
            {
                where1 += " and EQUITY_TRANSACTION.[SETTLEMENT_DATE] >= convert(datetime, '" + criteria.tranDateFrom + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where1 += " and EQUITY_TRANSACTION.[SETTLEMENT_DATE] <= convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }

            if (String.IsNullOrEmpty(criteria.max) == false)
            {
                //none
            }
            if (String.IsNullOrEmpty(criteria.min) == false)
            {
                //none
            }
            //------------------------------------------------------------------
            string where2 = "";
            if (int.Parse(criteria.company) != 0)
            {
                where2 += " and EQUITY_HEADER.EQUITY_COMPANY_ID=" + criteria.company;

            }
            //if (int.Parse(criteria.financialInstrument) != 0)
            //{
            //    query += " and DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID=" + criteria.financialInstrument;
            //}
            if (int.Parse(criteria.assetClass == null ? "0" : criteria.assetClass) != 0)
            {
                // where2 += " and DEPOSIT_HEADER.ASSET_CLASS_ID=" + criteria.assetClass;
            }
            if (int.Parse(criteria.issuer == null ? "0" : criteria.issuer) != 0)
            {
                where2 += " and upper(EQUITY_HEADER.SYMBOL) like'%" + criteria.issuer.ToUpper() + "%'";

            }
            if (int.Parse(criteria.keepAt == null ? "0" : criteria.keepAt) != 0)
            {
                //none
            }
            if (criteria.atatus != "0")
            {
                // where2 += " and DEPOSIT_TRANSACTION.TRANSACTION_TYPE	 ='" + criteria.atatus + "'";
            }
            if (int.Parse(criteria.related == null ? "0" : criteria.related) != 0)
            {
                where2 += " and  EQUITY_HEADER.[RELATED_ID] 	 ='" + criteria.atatus + "'";


            }

            if (int.Parse(criteria.timeToMaturityYearFrom) != 0 && int.Parse(criteria.timeToMaturityYearTo) != 0 &&
                int.Parse(criteria.timeToMaturityMonthFrom) != 0 && int.Parse(criteria.timeToMaturityMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;

                float m_t = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.timeToMaturityYearTo) + m_t;

                where2 += " and ROUND( CAST(DATEDIFF(month, EQUITY_PRIVILEGE.PAYMENT_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.timeToMaturityYearFrom) != 0 && int.Parse(criteria.timeToMaturityMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                where2 += " and ROUND( CAST(DATEDIFF(month, EQUITY_PRIVILEGE.PAYMENT_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where2 += " >=" + ym;

            }
            else if (int.Parse(criteria.timeToMaturityYearTo) != 0 && int.Parse(criteria.timeToMaturityMonthTo) != 0)
            {
                float m = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearTo) + m;
                where2 += " and ROUND( CAST(DATEDIFF(month, EQUITY_PRIVILEGE.PAYMENT_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where2 += " <=" + ym;
            }

            if (int.Parse(criteria.afterMaturedYearFrom) != 0 && int.Parse(criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedYearTo) != 0 && int.Parse(criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;
                ym_f *= -1;

                float m_t = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.selectAfterMaturedYearTo) + m_t;
                ym_t *= -1;
                where2 += " and ROUND( CAST(DATEDIFF(month, EQUITY_PRIVILEGE.PAYMENT_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.afterMaturedYearFrom) != 0 && int.Parse(criteria.afterMaturedMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                ym *= -1;
                where2 += " and  ROUND( CAST(DATEDIFF(month, EQUITY_PRIVILEGE.PAYMENT_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += " >=" + ym;
            }

            if (int.Parse(criteria.selectAfterMaturedYearTo) != 0 && int.Parse(criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.selectAfterMaturedYearTo) + m;
                ym *= -1;
                where2 += " and  ROUND( CAST(DATEDIFF(month, EQUITY_PRIVILEGE.PAYMENT_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += " <=" + ym;

            }

            if (String.IsNullOrEmpty(criteria.symbol) == false)
            {
                where2 += " and upper(EQUITY_HEADER.SYMBOL) like'%" + criteria.symbol.ToUpper() + "%'";
            }

            if (String.IsNullOrEmpty(criteria.tranDateFrom) == false && String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                // where2 += " and DEPOSIT_TRANSACTION.SETTLEMENT_DATE between convert(datetime, '" + criteria.tranDateFrom + "', 103) and ";
                // where2 += "  convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateFrom) == false)
            {
                // where2 += " and DEPOSIT_TRANSACTION.SETTLEMENT_DATE >= convert(datetime, '" + criteria.tranDateFrom + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                // where2 += " and DEPOSIT_TRANSACTION.SETTLEMENT_DATE <= convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }

            if (String.IsNullOrEmpty(criteria.max) == false)
            {
                //none
            }
            if (String.IsNullOrEmpty(criteria.min) == false)
            {
                //none
            }
            string query = String.Format(ReportQuery.queryEquity, where1, where2);
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
            IList<ReportEquityDTO> results = sqlQuery
                                .SetResultTransformer(Transformers.AliasToBean(typeof(ReportEquityDTO)))
                                .List<ReportEquityDTO>();
            return results;
        }

        public IList<ReportLoanDTO> GetReportLoan(ReportCriteriaDTO criteria)
        {
            string query = ReportQuery.loanQuery + "  ";
            string where = "";
            if (int.Parse(criteria.company) != 0)
            {
                where += " and LOANS_HEADER.COMPANY_ID=" + criteria.company;

            }
            //if (int.Parse(criteria.financialInstrument) != 0)
            //{
            //    query += " and DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID=" + criteria.financialInstrument;
            //}
            if (int.Parse(criteria.assetClass == null ? "0" : criteria.assetClass) != 0)
            {
                //none
            }

            if (int.Parse(criteria.issuer) != 0)
            {
                where += " and upper(LOANS_HEADER.COMPANY_NAME) like'%" + criteria.issuer.ToUpper() + "%'";

            }
            if (int.Parse(criteria.keepAt == null ? "0" : criteria.keepAt) != 0)
            {
                //none
            }
            if (criteria.atatus != "0")
            {
                //where += " and DEPOSIT_TRANSACTION.TRANSACTION_TYPE	 ='" + criteria.atatus + "'";
            }
            if (int.Parse(criteria.related == null ? "0" : criteria.related) != 0)
            {
                //none
            }

            if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0 &&
                int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;

                float m_t = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.timeToMaturityYearTo) + m_t;

                where += " and ROUND( CAST(DATEDIFF(month, LOANS_HEADER.CONTRACT_END_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                where += " and ROUND( CAST(DATEDIFF(month, LOANS_HEADER.CONTRACT_END_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where += " >=" + ym;

            }
            else if (int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearTo) + m;
                where += " and ROUND( CAST(DATEDIFF(month, LOANS_HEADER.CONTRACT_END_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where += " <=" + ym;
            }

            if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;
                ym_f *= -1;

                float m_t = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.selectAfterMaturedYearTo) + m_t;
                ym_t *= -1;
                where += " and ROUND( CAST(DATEDIFF(month, LOANS_HEADER.CONTRACT_END_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                ym *= -1;
                where += " and  ROUND( CAST(DATEDIFF(month, LOANS_HEADER.CONTRACT_END_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where += " >=" + ym;
            }

            if (int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.selectAfterMaturedYearTo) + m;
                ym *= -1;
                where += " and  ROUND( CAST(DATEDIFF(month, LOANS_HEADER.CONTRACT_END_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where += " <=" + ym;

            }

            if (String.IsNullOrEmpty(criteria.symbol) == false)
            {
                where += " and upper(LOANS_HEADER.CONTRACT_NUMBER) like '%" + criteria.symbol.ToUpper() + "%'";

            }

            if (String.IsNullOrEmpty(criteria.tranDateFrom) == false && String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where += " and LOANS_HEADER.CONTRACT_START_DATE between convert(datetime, '" + criteria.tranDateFrom + "', 103) and ";
                where += "  convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateFrom) == false)
            {
                where += " and LOANS_HEADER.CONTRACT_START_DATE >= convert(datetime, '" + criteria.tranDateFrom + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where += " and LOANS_HEADER.CONTRACT_START_DATE <= convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }

            if (String.IsNullOrEmpty(criteria.max) == false)
            {
                where += @"
                    and LOANS_INTEREST_DETAIL.INTEREST_RATE = (SELECT MAX(LOANS_INTEREST_DETAIL.INTEREST_RATE)
                      FROM LOANS_HEADER
                      left join LOANS_INTEREST_DETAIL on LOANS_HEADER.LOANS_HEADER_ID = LOANS_INTEREST_DETAIL.INTEREST_HEADER_ID
                      left join LOANS_EXTRAINSTALLMENT on LOANS_HEADER.LOANS_HEADER_ID = LOANS_EXTRAINSTALLMENT.LOANS_HEADER_ID
                      where 1=1 
                    " + where + ")";

            }
            else if (String.IsNullOrEmpty(criteria.min) == false)
            {
                where += @"
                    and LOANS_INTEREST_DETAIL.INTEREST_RATE = (SELECT MIN(LOANS_INTEREST_DETAIL.INTEREST_RATE)
                      FROM LOANS_HEADER
                      left join LOANS_INTEREST_DETAIL on LOANS_HEADER.LOANS_HEADER_ID = LOANS_INTEREST_DETAIL.INTEREST_HEADER_ID
                      left join LOANS_EXTRAINSTALLMENT on LOANS_HEADER.LOANS_HEADER_ID = LOANS_EXTRAINSTALLMENT.LOANS_HEADER_ID
                      where 1=1 
                    " + where + ")";
            }

            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query + where);
            IList<ReportLoanDTO> results = sqlQuery
                                .SetResultTransformer(Transformers.AliasToBean(typeof(ReportLoanDTO)))
                                .List<ReportLoanDTO>();



            return results;
        }

        public IList<ReportFixedIncomeDTO> GetReportFixedIncome(ReportCriteriaDTO criteria)
        {
            // -- G WHERE --------------------------------------------------------------------------------------------
            string where1 = "";
            if (int.Parse(criteria.company) != 0)
            {
                where1 += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID =" + criteria.company;

            }
            //if (int.Parse(criteria.financialInstrument) != 0)
            //{
            //    query += " and DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID=" + criteria.financialInstrument;
            //}
            if (int.Parse(criteria.assetClass == null ? "0" : criteria.assetClass) != 0)
            {
                // where1 += " and DEPOSIT_HEADER.ASSET_CLASS_ID=" + criteria.assetClass;
            }
            if (int.Parse(criteria.issuer) != 0)
            {
                where1 += " and upper(FIXINCOMESECURITIES_HEADER.ISSUER_NAME) like'%" + criteria.issuer.ToUpper() + "%'";

            }
            if (int.Parse(criteria.keepAt == null ? "0" : criteria.keepAt) != 0)
            {
                where1 += " and upper(FIXINCOMESECURITIES_PURCHASE.KEEP_AT) like'%" + criteria.issuer.ToUpper() + "%'";
            }
            if (criteria.atatus != "0")
            {
                //
            }
            if (int.Parse(criteria.related == null ? "0" : criteria.related) != 0)
            {
                where1 += " and FIXINCOMESECURITIES_PURCHASE.RELATED=" + criteria.company;

            }

            if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;

                float m_t = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.timeToMaturityYearTo) + m_t;

                where1 += " and ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                 && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                where1 += " and ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where1 += " >=" + ym;

            }
            else if (int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                  && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearTo) + m;
                where1 += " and ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where1 += " <=" + ym;
            }

            if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;
                ym_f *= -1;

                float m_t = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.selectAfterMaturedYearTo) + m_t;
                ym_t *= -1;
                where1 += " and ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                  && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                ym *= -1;
                where1 += " and  ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += " >=" + ym;
            }

            if (int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.selectAfterMaturedYearTo) + m;
                ym *= -1;
                where1 += " and  ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += " <=" + ym;

            }

            if (String.IsNullOrEmpty(criteria.symbol) == false)
            {
                where1 += " and upper(FIXINCOMESECURITIES_HEADER.SYMBOL) like'%" + criteria.symbol.ToUpper() + "%'";
            }

            if (String.IsNullOrEmpty(criteria.tranDateFrom) == false && String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where1 += " and FIXINCOMESECURITIES_PURCHASE.TRADE_DATE between convert(datetime, '" + criteria.tranDateFrom + "', 103) and ";
                where1 += "  convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateFrom) == false)
            {
                where1 += " and FIXINCOMESECURITIES_PURCHASE.TRADE_DATE >= convert(datetime, '" + criteria.tranDateFrom + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where1 += " and FIXINCOMESECURITIES_PURCHASE.TRADE_DATE <= convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }

            if (String.IsNullOrEmpty(criteria.max) == false)
            {
                where1 += @"
                    and FIXINCOMESECURITIES_PURCHASE.YIELD = (SELECT MAX(FIXINCOMESECURITIES_PURCHASE.YIELD)
                        FROM FIXINCOMESECURITIES_HEADER
		                inner join FIXINCOMESECURITIES_PURCHASE
		                on FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID
                      where 1=1 
                    " + where1 + ")";
            }
            if (String.IsNullOrEmpty(criteria.min) == false)
            {
                where1 += @"
                    and FIXINCOMESECURITIES_PURCHASE.YIELD = (SELECT MIN(FIXINCOMESECURITIES_PURCHASE.YIELD)
                        FROM FIXINCOMESECURITIES_HEADER
		                inner join FIXINCOMESECURITIES_PURCHASE
		                on FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_PURCHASE.FIXINCOMESECURITIES_HEADER_ID
                      where 1=1 
                    " + where1 + ")";
            }
            //------------------------------------------------------------------
            string where2 = "";
            if (int.Parse(criteria.company) != 0)
            {
                where2 += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID =" + criteria.company;

            }
            //if (int.Parse(criteria.financialInstrument) != 0)
            //{
            //    query += " and DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID=" + criteria.financialInstrument;
            //}
            if (int.Parse(criteria.assetClass == null ? "0" : criteria.assetClass) != 0)
            {
                // where2 += " and DEPOSIT_HEADER.ASSET_CLASS_ID=" + criteria.assetClass;
            }
            if (int.Parse(criteria.issuer == null ? "0" : criteria.issuer) != 0)
            {
                where2 += " and upper(FIXINCOMESECURITIES_HEADER.ISSUER_NAME) like'%" + criteria.issuer.ToUpper() + "%'";

            }
            if (int.Parse(criteria.keepAt == null ? "0" : criteria.keepAt) != 0)
            {
                //none
            }
            if (criteria.atatus != "0")
            {
                // where2 += " and DEPOSIT_TRANSACTION.TRANSACTION_TYPE	 ='" + criteria.atatus + "'";
            }
            if (int.Parse(criteria.related == null ? "0" : criteria.related) != 0)
            {
                //none
            }

            if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0 &&
                int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0 &&
                int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;

                float m_t = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.timeToMaturityYearTo) + m_t;

                where2 += " and ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0 && int.Parse(criteria.timeToMaturityMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                where2 += " and ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where2 += " >=" + ym;

            }
            else if (int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearTo) + m;
                where2 += " and ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where2 += " <=" + ym;
            }

            if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;
                ym_f *= -1;

                float m_t = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.selectAfterMaturedYearTo) + m_t;
                ym_t *= -1;
                where2 += " and ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                ym *= -1;
                where2 += " and  ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += " >=" + ym;
            }

            if (int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.afterMaturedMonthFrom) != 0 &&
                int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.selectAfterMaturedYearTo) + m;
                ym *= -1;
                where2 += " and  ROUND( CAST(DATEDIFF(month, FIXINCOMESECURITIES_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += " <=" + ym;

            }

            if (String.IsNullOrEmpty(criteria.symbol) == false)
            {
                where2 += " and upper(FIXINCOMESECURITIES_HEADER.SYMBOL) like'%" + criteria.symbol.ToUpper() + "%'";
            }

            if (String.IsNullOrEmpty(criteria.tranDateFrom) == false && String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where2 += " and FIXINCOMESECURITIES_HEADER.MATURITY_DATE between convert(datetime, '"
                           + criteria.tranDateFrom + "', 103) and ";
                where2 += "  convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateFrom) == false)
            {
                where2 += " and FIXINCOMESECURITIES_HEADER.MATURITY_DATE >= convert(datetime, '" + criteria.tranDateFrom + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where2 += " and FIXINCOMESECURITIES_HEADER.MATURITY_DATE <= convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }

            if (String.IsNullOrEmpty(criteria.max) == false)
            {
                where1 += @"
                    and FIXINCOMESECURITIES_TRANSACTION.YIELD = (SELECT MAX(FIXINCOMESECURITIES_TRANSACTION.YIELD)
                        FROM FIXINCOMESECURITIES_HEADER
		                inner join FIXINCOMESECURITIES_TRANSACTION
		                on FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID
                      where 1=1 
                    " + where1 + ")";
            }
            if (String.IsNullOrEmpty(criteria.min) == false)
            {
                where1 += @"
                    and FIXINCOMESECURITIES_TRANSACTION.YIELD = (SELECT MIN(FIXINCOMESECURITIES_TRANSACTION.YIELD)
                        FROM FIXINCOMESECURITIES_HEADER
		                inner join FIXINCOMESECURITIES_TRANSACTION
		                on FIXINCOMESECURITIES_HEADER.FIXINCOMESECURITIES_HEADER_ID = FIXINCOMESECURITIES_TRANSACTION.FIXINCOMESECURITIES_HEADER_ID
                      where 1=1 
                    " + where1 + ")";
            }
            string query = String.Format(ReportQuery.queryFixedIncome, where1, where2);
            // ---END G WHERE-----------------------------------------------------------------------------------------
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
            IList<ReportFixedIncomeDTO> results = sqlQuery
                                .SetResultTransformer(Transformers.AliasToBean(typeof(ReportFixedIncomeDTO)))
                                .List<ReportFixedIncomeDTO>();
            return results;
        }

        public IList<ReportUnitTrustsDTO> GetReportUnitTrusts(ReportCriteriaDTO criteria)
        {

            // --- G condition ------------
            string where1 = "";
            if (int.Parse(criteria.company) != 0)
            {
                where1 += " and  UNIT_TRUST_HEADER.UNIT_TRUST_COMPANY_ID =" + criteria.company;

            }
            //if (int.Parse(criteria.financialInstrument) != 0)
            //{
            //    query += " and DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID=" + criteria.financialInstrument;
            //}
            if (int.Parse(criteria.assetClass == null ? "0" : criteria.assetClass) != 0)
            {
                // where1 += " and DEPOSIT_HEADER.ASSET_CLASS_ID=" + criteria.assetClass;
            }
            if (int.Parse(criteria.issuer == null ? "0" : criteria.issuer) != 0)
            {
                where1 += " and upper(UNIT_TRUST_HEADER.SYMBOL) like'%" + criteria.issuer.ToUpper() + "%'";

            }
            if (int.Parse(criteria.keepAt == null ? "0" : criteria.keepAt) != 0)
            {
                //none
            }
            if (criteria.atatus != "0")
            {
                where1 += " and UNIT_TRUST_TRANSACTION.[TRANSACTION_TYPE]	 ='" + criteria.atatus + "'";
            }
            if (int.Parse(criteria.related) != 0)
            {
                //none
            }

            if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;

                float m_t = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.timeToMaturityYearTo) + m_t;

                where1 += " and ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                where1 += " and ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where1 += " >=" + ym;

            }
            else if (int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearTo) + m;
                where1 += " and ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where1 += " <=" + ym;
            }

            if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;
                ym_f *= -1;

                float m_t = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.selectAfterMaturedYearTo) + m_t;
                ym_t *= -1;
                where1 += " and ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                ym *= -1;
                where1 += " and  ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += " >=" + ym;
            }

            if (int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.selectAfterMaturedYearTo) + m;
                ym *= -1;
                where1 += " and  ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.MATURITY_DATE, GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where1 += " <=" + ym;

            }

            if (String.IsNullOrEmpty(criteria.symbol) == false)
            {
                where1 += " and upper(UNIT_TRUST_HEADER.[SYMBOL]) like '%" + criteria.symbol.ToUpper() + "%'";
            }

            if (String.IsNullOrEmpty(criteria.tranDateFrom) == false && String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where1 += " and UNIT_TRUST_TRANSACTION.[SETTLEMENT_DATE] between convert(datetime, '" + criteria.tranDateFrom + "', 103) and ";
                where1 += "  convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateFrom) == false)
            {
                where1 += " and UNIT_TRUST_TRANSACTION.[SETTLEMENT_DATE] >= convert(datetime, '" + criteria.tranDateFrom + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where1 += " and UNIT_TRUST_TRANSACTION.[SETTLEMENT_DATE] <= convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }

            if (String.IsNullOrEmpty(criteria.max) == false)
            {
                //none
            }
            if (String.IsNullOrEmpty(criteria.min) == false)
            {
                //none
            }
            //------------------------------------------------------------------
            string where2 = "";
            if (int.Parse(criteria.company) != 0)
            {
                where2 += " and UNIT_TRUST_HEADER.UNIT_TRUST_COMPANY_ID = " + criteria.company;

            }
            //if (int.Parse(criteria.financialInstrument) != 0)
            //{
            //    query += " and DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID=" + criteria.financialInstrument;
            //}
            if (int.Parse(criteria.assetClass == null ? "0" : criteria.assetClass) != 0)
            {
                // where2 += " and DEPOSIT_HEADER.ASSET_CLASS_ID=" + criteria.assetClass;
            }
            if (int.Parse(criteria.issuer == null ? "0" : criteria.issuer) != 0)
            {
                where2 += " and upper(UNIT_TRUST_HEADER.[ASSET_MGMT_NAME]) like'%" + criteria.issuer.ToUpper() + "%'";

            }
            if (int.Parse(criteria.keepAt == null ? "0" : criteria.keepAt) != 0)
            {
                //none
            }
            if (criteria.atatus != "0")
            {
                //  where2 += " and DEPOSIT_TRANSACTION.TRANSACTION_TYPE	 ='" + criteria.atatus + "'";
            }
            if (int.Parse(criteria.related) != 0)
            {
                //none
            }

            if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;

                float m_t = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.timeToMaturityYearTo) + m_t;

                where2 += " and ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                    && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                where2 += " and ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where2 += " >=" + ym;

            }
            else if (int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                  && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearTo) + m;
                where2 += " and ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where2 += " <=" + ym;
            }

            if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;
                ym_f *= -1;

                float m_t = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.selectAfterMaturedYearTo) + m_t;
                ym_t *= -1;
                where2 += " and ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                 && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                ym *= -1;
                where2 += " and  ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += " >=" + ym;
            }

            if (int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.selectAfterMaturedYearTo) + m;
                ym *= -1;
                where2 += " and  ROUND( CAST(DATEDIFF(month, UNIT_TRUST_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where2 += " <=" + ym;

            }

            if (String.IsNullOrEmpty(criteria.symbol) == false)
            {
                where2 += " and upper(UNIT_TRUST_HEADER.[SYMBOL]) like '%" + criteria.symbol.ToUpper() + "%'";
            }

            if (String.IsNullOrEmpty(criteria.tranDateFrom) == false
                && String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where2 += " and unit_trust_privilege.PAYMENT_DATE between convert(datetime, '" + criteria.tranDateFrom + "', 103) and ";
                where2 += "  convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateFrom) == false)
            {
                where2 += " and unit_trust_privilege.PAYMENT_DATE >= convert(datetime, '" + criteria.tranDateFrom + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where2 += " and unit_trust_privilege.PAYMENT_DATE <= convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }

            if (String.IsNullOrEmpty(criteria.max) == false)
            {
                //none
            }
            if (String.IsNullOrEmpty(criteria.min) == false)
            {
                //none
            }
            string query = String.Format(ReportQuery.queryUnitTrusts, where1, where2);
            // ----------------------------
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
            IList<ReportUnitTrustsDTO> results = sqlQuery
                                .SetResultTransformer(Transformers.AliasToBean(typeof(ReportUnitTrustsDTO)))
                                .List<ReportUnitTrustsDTO>();
            return results;
        }

        public IList<ReportDepositAtFinancialDTO> GetReportDepositAtFinancial(ReportCriteriaDTO criteria)
        {
            string query = ReportQuery.queryDepositFinance + "  ";
            string where = "";
            if (int.Parse(criteria.company) != 0)
            {
                where += " and DEPOSIT_HEADER.DEPOSIT_COMPANY_ID=" + criteria.company;

            }
            //if (int.Parse(criteria.financialInstrument) != 0)
            //{
            //    query += " and DEPOSIT_HEADER.FINANCIAL_INSTRUMENT_TYPE_ID=" + criteria.financialInstrument;
            //}
            if (int.Parse(criteria.assetClass == null ? "0" : criteria.assetClass) != 0)
            {
                where += " and DEPOSIT_HEADER.ASSET_CLASS_ID=" + criteria.assetClass;
            }
            if (int.Parse(criteria.issuer == null ? "0" : criteria.issuer) != 0)
            {
                where += " and upper(DEPOSIT_HEADER.SYMBOL) like'%" + criteria.issuer.ToUpper() + "%'";

            }
            if (int.Parse(criteria.keepAt == null ? "0" : criteria.keepAt) != 0)
            {
                //none
            }
            if (criteria.atatus != "0")
            {
                where += " and DEPOSIT_TRANSACTION.TRANSACTION_TYPE	 ='" + criteria.atatus + "'";
            }
            if (int.Parse(criteria.related == null ? "0" : criteria.related) != 0)
            {
                //none
            }

            if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;

                float m_t = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.timeToMaturityYearTo) + m_t;

                where += " and ROUND( CAST(DATEDIFF(month, DEPOSIT_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.timeToMaturityYearFrom == null ? "0" : criteria.timeToMaturityYearFrom) != 0
                && int.Parse(criteria.timeToMaturityMonthFrom == null ? "0" : criteria.timeToMaturityMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                where += " and ROUND( CAST(DATEDIFF(month, DEPOSIT_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where += " >=" + ym;

            }
            else if (int.Parse(criteria.timeToMaturityYearTo == null ? "0" : criteria.timeToMaturityYearTo) != 0
                && int.Parse(criteria.timeToMaturityMonthTo == null ? "0" : criteria.timeToMaturityMonthTo) != 0)
            {
                float m = int.Parse(criteria.timeToMaturityMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearTo) + m;
                where += " and ROUND( CAST(DATEDIFF(month, DEPOSIT_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";

                where += " <=" + ym;
            }

            if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.selectAfterMaturedYearTo) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.selectAfterMaturedMonthTo) != 0)
            {
                float m_f = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym_f = int.Parse(criteria.timeToMaturityYearFrom) + m_f;
                ym_f *= -1;

                float m_t = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym_t = int.Parse(criteria.selectAfterMaturedYearTo) + m_t;
                ym_t *= -1;
                where += " and ROUND( CAST(DATEDIFF(month, DEPOSIT_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where += String.Format(" between {0} and {1}", ym_f, ym_t);

            }
            else if (int.Parse(criteria.afterMaturedYearFrom == null ? "0" : criteria.afterMaturedYearFrom) != 0
                && int.Parse(criteria.afterMaturedMonthFrom == null ? "0" : criteria.afterMaturedMonthFrom) != 0)
            {

                float m = int.Parse(criteria.timeToMaturityMonthFrom) / 12 * 10;
                float ym = int.Parse(criteria.timeToMaturityYearFrom) + m;
                ym *= -1;
                where += " and  ROUND( CAST(DATEDIFF(month, DEPOSIT_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where += " >=" + ym;
            }

            if (int.Parse(criteria.selectAfterMaturedYearTo == null ? "0" : criteria.afterMaturedMonthFrom) != 0
                && int.Parse(criteria.selectAfterMaturedMonthTo == null ? "0" : criteria.afterMaturedMonthFrom) != 0)
            {
                float m = int.Parse(criteria.selectAfterMaturedMonthTo) / 12 * 10;
                float ym = int.Parse(criteria.selectAfterMaturedYearTo) + m;
                ym *= -1;
                where += " and  ROUND( CAST(DATEDIFF(month, DEPOSIT_HEADER.[MATURITY_DATE], GETDATE())AS FLOAT)/CAST(12 AS FLOAT),2 )";
                where += " <=" + ym;

            }

            if (String.IsNullOrEmpty(criteria.symbol) == false)
            {
                where += " and upper(DEPOSIT_HEADER.SYMBOL) like'%" + criteria.issuer.ToUpper() + "%'";
            }

            if (String.IsNullOrEmpty(criteria.tranDateFrom) == false && String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where += " and DEPOSIT_TRANSACTION.SETTLEMENT_DATE between convert(datetime, '" + criteria.tranDateFrom + "', 103) and ";
                where += "  convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateFrom) == false)
            {
                where += " and DEPOSIT_TRANSACTION.SETTLEMENT_DATE >= convert(datetime, '" + criteria.tranDateFrom + "', 103) ";

            }
            else if (String.IsNullOrEmpty(criteria.tranDateTo) == false)
            {
                where += " and DEPOSIT_TRANSACTION.SETTLEMENT_DATE <= convert(datetime, '" + criteria.tranDateTo + "', 103) ";

            }

            if (String.IsNullOrEmpty(criteria.max) == false)
            {
                //none
            }
            if (String.IsNullOrEmpty(criteria.min) == false)
            {
                //none
            }

            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query + where);
            IList<ReportDepositAtFinancialDTO> results = sqlQuery
                                .SetResultTransformer(Transformers.AliasToBean(typeof(ReportDepositAtFinancialDTO)))
                                .List<ReportDepositAtFinancialDTO>();
            return results;
        }

        #endregion

 

   


        public IList<R3302DTO> R3302(ReportR3CriteriaDTO criteria)
        {
            /*
            //2 level
            string query = " ";
            //if (int.Parse(criteria.company) != 0)
            //{
            //    query += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

            //}
            //string sql = String.Format(R3Query.r3302header, query);
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3302header);
            IList<R3302DTO> results = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3302DTO)))
                .List<R3302DTO>();
           // int i = 0;
            foreach (R3302DTO header in results)
            {

                //string sqlExtends = "where FIXINCOMESECURITIES_HEADER.ISSUER_ID=" + header.ISSUER_ID;  
                string sqlExtends = "and ISSUER_TYPE_ID=" + header.ISSUER_TYPE_ID;  

                if (int.Parse(criteria.company) != 0)
                {
                    sqlExtends += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;
                }
                
                if (criteria.dateAsOf!="")
                {
                    sqlExtends += " and CONVERT(VARCHAR(10),FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE,103) ='" + criteria.company+"'";
                }

                if (criteria.year != "")
                {
                    sqlExtends += " and DATEPART(year,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE) ='" + criteria.year + "'";
                }
                query = R3Query.r3302detail + " " + sqlExtends;
                sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                header.Detail = sqlQuery
      .SetResultTransformer(Transformers.AliasToBean(typeof(R3302DetailDTO))).List<R3302DetailDTO>().ToList();
            }

            return results;
             */

            R3302DTO header =               new R3302DTO();
            IList<R3302DTO> results = new List<R3302DTO>();
            results.Add(header);

            string whereQrieria = "";
            if (int.Parse(criteria.company) != 0)
            {
                whereQrieria += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

            }
            if (criteria.dateAsOf != "")
            {
                whereQrieria += " and CONVERT(VARCHAR(10),FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE,103) ='" + criteria.dateAsOf + "' ";
            }

            if (criteria.year != "")
            {
                whereQrieria += " and DATEPART(year,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE) ='" + criteria.year + "' ";
            }


            string where = @"
            and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID = 10

            ";
            IList<R3302DTO> list1 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3302qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3302DTO))).List<R3302DTO>();
            header.Detail = list1.ToList();


            where = @"
            and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID = 11 or FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID = 12

            ";
            IList<R3302DTO> list2 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3302qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3302DTO))).List<R3302DTO>();
            header.Detail2 = list2.ToList();


            where = @"
            and  FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID = 13

            ";
            IList<R3302DTO> list3 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3302qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3302DTO))).List<R3302DTO>();
            header.Detail3 = list3.ToList();


            where = @"
       and      FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID = 69

            ";
            IList<R3302DTO> list4 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3302qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3302DTO))).List<R3302DTO>();
            header.Detail4 = list4.ToList();


            return results;
        }



        #region IReportService Members

        /*
        public IList<R3303DTO> R3303(ReportR3CriteriaDTO criteria)
        {
            //4 level

            string where = " ";
            if (criteria.dateAsOf != "")
            {
                where += " and CONVERT(VARCHAR(10),FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE,103) ='" + criteria.company + "' ";
            }

            if (criteria.year != "")
            {
                where += " and DATEPART(year,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE) ='" + criteria.year + "' ";
            }

            string query = R3Query.r3303qry1;
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
            IList<R3303DTO> results = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            foreach (R3303DTO level1 in results)
            {
                query = String.Format(R3Query.r3303qry2, " and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID=" + level1.ISSUER_TYPE_ID + where);
                sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                level1.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>().ToList();
                //-- level 2
                foreach (R3303DTO level2 in level1.Detail)
                {


                    query = String.Format(R3Query.r3303qry3, " and ISSUER_TYPE_ID=" + level2.ISSUER_TYPE_ID + " and ASSET_CLASS_ID=" + level2.ASSET_CLASS_ID + where);


                    sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                    level2.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>().ToList();
                    if (level2.Detail.Count > 0)
                    {
                        //-- level 3
                        foreach (R3303DTO level3 in level2.Detail)
                        {
                            //--- criteria ------------------------------------------------------------------------------------
                            string sqlExtends = " and ISSUER_TYPE_ID=" + level3.ISSUER_TYPE_ID + " and ASSET_CLASS_ID=" +
                                                  level3.ASSET_CLASS_ID + " and RELATED='" + level3.RELATED + "' " + where;// 
                            if (int.Parse(criteria.company) != 0)
                            {
                                sqlExtends += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

                            }
                            //-- add other criteria
                            query = String.Format(R3Query.r3303qry4 , sqlExtends);
                            //-------------------------------------------------------------------------------------------------
                            
                            
                            
                            
                            
                            sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query + " " + sqlExtends);
                            level3.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>().ToList();
                            Console.WriteLine();
                        }
                    }
                    else
                    {
                        //--- criteria ------------------------------------------------------------------------------------
                        string sqlExtends = " and ISSUER_TYPE_ID=" + level2.ISSUER_TYPE_ID + " and ASSET_CLASS_ID=" + level2.ASSET_CLASS_ID + where;
                        if (int.Parse(criteria.company) != 0)
                        {
                            sqlExtends += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

                        }
                        //-- add other criteria
                        query = String.Format(R3Query.r3303qry4, sqlExtends) ;
                        //-------------------------------------------------------------------------------------------------
                        sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                        level2.Detail2 = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>().ToList();
                        Console.WriteLine();
                    }

                }
            }

            return results;
        }
        */

        public IList<R3303DTO> R3303(ReportR3CriteriaDTO criteria)
        {
            R3303DTO header = new R3303DTO();
            IList<R3303DTO> results = new List<R3303DTO>();
            results.Add(header);

            string whereQrieria = "";
            if (int.Parse(criteria.company) != 0)
            {
                whereQrieria += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

            }
            if (criteria.dateAsOf != "")
            {
                whereQrieria += " and CONVERT(VARCHAR(10),FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE,103) ='" + criteria.dateAsOf + "' ";
            }

            if (criteria.year != "")
            {
                whereQrieria += " and DATEPART(year,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE) ='" + criteria.year + "' ";
            }

            string where = @"
            and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (10,11,12,13) 
            and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 7
            ";
            IList<R3303DTO> list11 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();
            header.Detail = list11.ToList();


            where = @"
            and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (10,11,12,13) 
            and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 10
            ";
            IList<R3303DTO> list12 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail2 = list12.ToList();


            where = @"
            and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (14) 
            and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 7
            and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Related'
            ";
            IList<R3303DTO> list1211 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail3 = list1211.ToList();


            where = @"
            and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (14) 
            and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 7
            and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Non Related'
            ";
            IList<R3303DTO> list1212 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail4 = list1212.ToList();


            where = @"
            and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (14) 
            and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 10
            and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Related'
            ";
            IList<R3303DTO> list1221 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail5 = list1221.ToList();

            where = @"
            and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (14) 
and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 10
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Non Related'
            ";
            IList<R3303DTO> list1222 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail6 = list1222.ToList();

            where = @"
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (69) 
and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 7
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Related'
            ";
            IList<R3303DTO> list311 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail7= list311.ToList();


            where = @"
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (69) 
and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 7
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Non Related'
            ";
            IList<R3303DTO> list312 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail8 = list312.ToList();

            where = @"
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (69) 
and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 10
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Related'
            ";
            IList<R3303DTO> list321 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail9 = list321.ToList();

            where = @"
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in (69) 
and FIXINCOMESECURITIES_HEADER.ASSET_CLASS_ID = 10
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Non Related'
            ";
            IList<R3303DTO> list322 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3303qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3303DTO))).List<R3303DTO>();

            header.Detail10= list322.ToList();

            return results;
        }

        public IList<R3306DTO> R3306(ReportR3CriteriaDTO criteria)
        {
            R3306DTO header = new R3306DTO();
            IList<R3306DTO> results = new List<R3306DTO>();
            results.Add(header);

            string whereQrieria = "";
            if (int.Parse(criteria.company) != 0)
            {
                whereQrieria += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

            }
            if (criteria.dateAsOf != "")
            {
                whereQrieria += " and CONVERT(VARCHAR(10),FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE,103) <='" + criteria.dateAsOf + "' ";
            }

            if (criteria.year != "")
            {
                whereQrieria += " and DATEPART(year,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE) ='" + criteria.year + "' ";
            }


            string where = @"
            and (FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Debentures' or FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Convertible Debentures')
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in(10,11,12,13)

            ";
            IList<R3306DTO> list1 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3306qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3306DTO))).List<R3306DTO>();
            header.Detail = list1.ToList();


            where = @"
            and (FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Debentures' or FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Convertible Debentures')
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in(14)
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Related'

            ";
            IList<R3306DTO> list2 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3306qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3306DTO))).List<R3306DTO>();
            header.Detail2 = list2.ToList();


            where = @"
            and (FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Debentures' or FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Convertible Debentures')
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in(14)
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Non Related'

            ";
            IList<R3306DTO> list3 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3306qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3306DTO))).List<R3306DTO>();
            header.Detail3 = list3.ToList();


            where = @"
       and     (FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Debentures' or FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Convertible Debentures')
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in(69)
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Related'

            ";
            IList<R3306DTO> list4 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3306qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3306DTO))).List<R3306DTO>();
            header.Detail4 = list4.ToList();

            where = @"
    and       (FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Debentures' or FIXINCOMESECURITIES_HEADER.ASSET_CLASS_EN = 'Convertible Debentures')
and FIXINCOMESECURITIES_HEADER.ISSUER_TYPE_ID in(69)
and FIXINCOMESECURITIES_PURCHASE.RELATED = 'Non Related'

            ";
            IList<R3306DTO> list5 = SessionManager.CurrentSession.CreateSQLQuery(R3Query.r3306qry + where + whereQrieria).SetResultTransformer(Transformers.AliasToBean(typeof(R3306DTO))).List<R3306DTO>();
            header.Detail5 = list5.ToList();


            return results;
        }
        /*
        public IList<R3306DTO> R3306(ReportR3CriteriaDTO criteria)
        {
            //3 level

            string where = " ";
            if (criteria.dateAsOf != "")
            {
                where += " and CONVERT(VARCHAR(10),FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE,103) ='" + criteria.company + "' ";
            }

            if (criteria.year != "")
            {
                where += " and DATEPART(year,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE) ='" + criteria.year + "' ";
            }

            string query = R3Query.r3306qry1;
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
            IList<R3306DTO> results = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3306DTO))).List<R3306DTO>();

            foreach (R3306DTO level1 in results)
            {
                query = String.Format(R3Query.r3306qry2, " and ISSUER_TYPE_ID=" + level1.ISSUER_TYPE_ID+where);
                sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                level1.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3306DTO))).List<R3306DTO>().ToList();
                foreach (R3306DTO level2 in level1.Detail)
                {
                    //--- criteria ------------------------------------------------------------------------------------
                    string sqlExtends = " and ISSUER_TYPE_ID=" + level2.ISSUER_TYPE_ID + " and RELATED=" + level2.RELATED + where;
                    if (int.Parse(criteria.company) != 0)
                    {
                        sqlExtends += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

                    }
                    //-- add other criteria
                    query = String.Format(R3Query.r3306qry3, sqlExtends);
                    //-------------------------------------------------------------------------------------------------
                    
                    sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                    level2.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3306DTO))).List<R3306DTO>().ToList();                   

                }
            }

            return results;
        }
        */
        #endregion

        #region IReportService Members


        public IList<R3305DTO> R3305(ReportR3CriteriaDTO criteria)
        {//3 level

            string where = " ";
            if (criteria.dateAsOf != "")
            {
                where += " and CONVERT(VARCHAR(10),FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE,103) ='" + criteria.company + "' ";
            }

            if (criteria.year != "")
            {
                where += " and DATEPART(year,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE) ='" + criteria.year + "' ";
            }

            string query = R3Query.r3306qry1;
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
            IList<R3305DTO> results = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3305DTO))).List<R3305DTO>();

            foreach (R3305DTO level1 in results)
            {
                query = String.Format(R3Query.r3306qry2, " and ISSUER_TYPE_ID=" + level1.ISSUER_TYPE_ID + where);
                sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                level1.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3305DTO))).List<R3305DTO>().ToList();
                foreach (R3305DTO level2 in level1.Detail)
                {
                    //--- criteria ------------------------------------------------------------------------------------
                    string sqlExtends = " and ISSUER_TYPE_ID=" + level2.ISSUER_TYPE_ID + " and RELATED=" + level2.RELATED;
                    if (int.Parse(criteria.company) != 0)
                    {
                        sqlExtends += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

                    }
                    //-- add other criteria
                    query = String.Format(R3Query.r3305qry3, sqlExtends);
                    //-------------------------------------------------------------------------------------------------

                    sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                    level2.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3305DTO))).List<R3305DTO>().ToList();

                }
            }

            return results;
        }

        public IList<R3307DTO> R3307(ReportR3CriteriaDTO criteria)
        {//3 level

            string where = " ";
            if (criteria.dateAsOf != "")
            {
                where += " and CONVERT(VARCHAR(10),FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE,103) ='" + criteria.company + "' ";
            }

            if (criteria.year != "")
            {
                where += " and DATEPART(year,FIXINCOMESECURITIES_PURCHASE.SETTLEMENT_DATE) ='" + criteria.year + "' ";
            }

            string query = R3Query.r3306qry1;
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
            IList<R3307DTO> results = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3307DTO))).List<R3307DTO>();

            foreach (R3307DTO level1 in results)
            {
                query = String.Format(R3Query.r3306qry2, " and ISSUER_TYPE_ID=" + level1.ISSUER_TYPE_ID+where);
                sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                level1.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3307DTO))).List<R3307DTO>().ToList();
                foreach (R3307DTO level2 in level1.Detail)
                {
                    //--- criteria ------------------------------------------------------------------------------------
                    string sqlExtends = " and ISSUER_TYPE_ID=" + level2.ISSUER_TYPE_ID + " and RELATED=" + level2.RELATED;
                    if (int.Parse(criteria.company) != 0)
                    {
                        sqlExtends += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

                    }
                    //-- add other criteria
                    query = String.Format(R3Query.r3307qry3, sqlExtends);
                    //-------------------------------------------------------------------------------------------------

                    sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                    level2.Detail = sqlQuery.SetResultTransformer(Transformers.AliasToBean(typeof(R3307DTO))).List<R3307DTO>().ToList();

                }
            }

            return results;
        }

        public IList<R3520DTO> R3520(ReportR3CriteriaDTO criteria)
        {
            //2 level
            string query = R3Query.r3520header;
            ISQLQuery sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
            IList<R3520DTO> results = sqlQuery
      .SetResultTransformer(Transformers.AliasToBean(typeof(R3520DTO)))
      .List<R3520DTO>();

            foreach (R3520DTO header in results)
            {
                /*
                string sqlExtends = "where FIXINCOMESECURITIES_HEADER.ISSUER_ID=" + header.ISSUER_ID;
                if (int.Parse(criteria.company) != 0)
                {
                    sqlExtends += " and FIXINCOMESECURITIES_HEADER.COMPANY_ID = " + criteria.company;

                }
                 */
                query = R3Query.r3520detail + " " ;
                sqlQuery = SessionManager.CurrentSession.CreateSQLQuery(query);
                header.Detail = sqlQuery
      .SetResultTransformer(Transformers.AliasToBean(typeof(R3520DetailDTO))).List<R3520DetailDTO>().ToList();
            }

            return results;
        }

        public IList<R3710DTO> R3710(ReportR3CriteriaDTO criteria)
        {
            throw new NotImplementedException();
        }


        public IList<R3100DTO> R3100(ReportR3CriteriaDTO criteria)
        {
            throw new NotImplementedException();
        }

        public IList<R3200DTO> R3200(ReportR3CriteriaDTO criteria)
        {
            throw new NotImplementedException();
        }

        public IList<R3300DTO> R3300(ReportR3CriteriaDTO criteria)
        {
            throw new NotImplementedException();
        }

        public IList<R3301DTO> R3301(ReportR3CriteriaDTO criteria)
        {
            throw new NotImplementedException();
        }

       
        #endregion
    }
}
